//-----------------------------------------------------------------------------
//クラス名：AttendanceHistoryMonth.java
//所    属：H3A1 04
//名    前：大瀬碩
//説    明：月の出席履歴を表示する
//-----------------------------------------------------------------------------
package attendance_history;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Properties;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
//import javax.servlet.annotation.WebServlet;


//@WebServlet("/AttendanceHistoryMonth")
public class AttendanceHistoryMonth extends HttpServlet {
	private static final long serialVersionUID = 1L;
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
    }

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		 	response.setContentType("text/html; charset=UTF-8");
		 	
		 	Object i = request.getParameter("sl_month");
		 	//月選択されていたらその月の履歴を表示する
		 	if(i==null){
		 	
			 	HttpSession session = request.getSession(true);
			 	
			 	Calendar calendar = Calendar.getInstance();
			 	int year = calendar.get(Calendar.YEAR);
			    int month = calendar.get(Calendar.MONTH);
			    month=month+1;
			    request.setAttribute("month", month);
			    
			    //DB接続
			    ServletContext context = this.getServletContext();
				String path = context.getRealPath("/WEB-INF/connect.properties");
				
				InputStream in = new FileInputStream(path);
				Properties properties = new Properties();
				properties.load(in);
				in.close();
			    
			    Connection conn = null;
			    String url = properties.getProperty("key1");
			    String user = properties.getProperty("key2");
			    String password = properties.getProperty("key3");
			    
			    Object id = session.getAttribute("id");
			    Object name = session.getAttribute("name");
			    Object an = session.getAttribute("a_number");
			    Object class_id = session.getAttribute("class");
			    
			    int at=0;
			    int ta=0;
			    int af=0;
			    int acflag=0;
			    
			    request.setAttribute("name",name);
			    request.setAttribute("an",an);
			    
			    request.setAttribute("tardy", ta);
			    request.setAttribute("afs", af);
			    request.setAttribute("attendance", at);
			    request.setAttribute("sc", acflag);
			    
			    try {
			      Class.forName("com.mysql.jdbc.Driver").newInstance();
			      conn = DriverManager.getConnection(url, user, password);
			      
			      Statement stmt = conn.createStatement();
			      
			      String sql = "SELECT * FROM class WHERE class_id = "+class_id;
			      ResultSet rs = stmt.executeQuery(sql);
			      while(rs.next()){
			    	  request.setAttribute("id",rs.getString("class_name"));
			      }
			      
			      //DBから情報を取得
			      sql = "SELECT * FROM attendance_records WHERE date BETWEEN '"+year+"/"+month+"/1' AND '"+year+"/"+month+"/31'"+
			    	      " AND student_id = "+id;
			      rs = stmt.executeQuery(sql);
			      
			      while(rs.next()){
			        String status = rs.getString("status_id");
			        if(status.equals("1")){
			        	at++;
			        	request.setAttribute("attendance", at);
			        }
			        if(status.equals("2")){
			        	ta++;
			        	request.setAttribute("tardy", ta);
			        }
			        if(status.equals("3")){
			        	af++;
			        	request.setAttribute("afs", af);
			        }
			        if(status.equals("4")){
			        	at++;
			        	request.setAttribute("attendance", at);
			        }
			      }
			      
			      //補講回数を取得
			      sql = "SELECT * FROM makeup_class WHERE acceptance_date BETWEEN '"+year+"/"+month+"/1' AND '"+year+"/"+month+"/31'"+
			    	      " AND student_id = "+id;
			      rs = stmt.executeQuery(sql);
			      
			      
			      while(rs.next()){
			    	  String status = rs.getString("acceptance_flag");
				        if(status.equals("1")){
				        	acflag++;
				        	request.setAttribute("sc", acflag);
				        }
				        if(status.equals("2")){
				        	acflag=acflag+2;
				        	request.setAttribute("sc", acflag);
				        }
				        if(status.equals("3")){
				        	acflag=acflag+3;
				        	request.setAttribute("sc", acflag);
				        }
				        if(status.equals("4")){
				        	acflag=acflag+4;
				        	request.setAttribute("sc", acflag);
				        }
			      }
			      
			      rs.close();
			      stmt.close();
			    }catch (ClassNotFoundException e){
			    	
			    }catch (SQLException e){
			      
			    }catch (Exception e){
			    	
			    }finally{
			      try{
			        if (conn != null){
			          conn.close();
			        }
			      }catch (SQLException e){
			        
			      }
			    }
			    
			    RequestDispatcher rd = request.getRequestDispatcher("AttendanceHistoryMonth.jsp");
				rd.forward(request,response);
		 	}
		 	//月選択されていなかったら今月の出席履歴を表示
		 	else{
		 		HttpSession session = request.getSession(true);
			 	
			 	Calendar calendar = Calendar.getInstance();
			 	int year = calendar.get(Calendar.YEAR);
			    int month = calendar.get(Calendar.MONTH);
			    month=month+1;
			    request.setAttribute("month", i);
			    
			    //DB接続
			    ServletContext context = this.getServletContext();
				String path = context.getRealPath("/WEB-INF/connect.properties");
				
				InputStream in = new FileInputStream(path);
				Properties properties = new Properties();
				properties.load(in);
				in.close();
			    
			    Connection conn = null;
			    String url = properties.getProperty("key1");
			    String user = properties.getProperty("key2");
			    String password = properties.getProperty("key3");
			    
			    Object id = session.getAttribute("id");
			    Object name = session.getAttribute("name");
			    Object an = session.getAttribute("a_number");
			    Object class_id = session.getAttribute("class");
			    
			    int at=0;
			    int ta=0;
			    int af=0;
			    int acflag=0;
			    
			    request.setAttribute("name",name);
			    request.setAttribute("an",an);
			    
			    request.setAttribute("tardy", ta);
			    request.setAttribute("afs", af);
			    request.setAttribute("attendance", at);
			    request.setAttribute("sc", acflag);
			    
			    try {
			      Class.forName("com.mysql.jdbc.Driver").newInstance();
			      conn = DriverManager.getConnection(url, user, password);
	
			      Statement stmt = conn.createStatement();
			      
			      String sql = "SELECT * FROM class WHERE class_id = "+class_id;
			      ResultSet rs = stmt.executeQuery(sql);
			      while(rs.next()){
			    	  request.setAttribute("id",rs.getString("class_name"));
			      }
			      
			      //DBから情報を取得
			      sql = "SELECT * FROM attendance_records WHERE date BETWEEN '"+year+"/"+i+"/1' AND '"+year+"/"+i+"/31'"+
			    	      " AND student_id = "+id;
			      rs = stmt.executeQuery(sql);
			      
			      while(rs.next()){
			        String status = rs.getString("status_id");
			        if(status.equals("1")){
			        	at++;
			        	request.setAttribute("attendance", at);
			        }
			        if(status.equals("2")){
			        	ta++;
			        	request.setAttribute("tardy", ta);
			        }
			        if(status.equals("3")){
			        	af++;
			        	request.setAttribute("afs", af);
			        }
			        if(status.equals("4")){
			        	at++;
			        	request.setAttribute("attendance", at);
			        }
			      }
			      
			      //補講回数を取得
			      sql = "SELECT * FROM makeup_class WHERE acceptance_date BETWEEN '"+year+"/"+i+"/1' AND '"+year+"/"+i+"/31'"+
			    	      " AND student_id = "+id;
			      rs = stmt.executeQuery(sql);
			      
			      
			      while(rs.next()){
			    	  String status = rs.getString("acceptance_flag");
				        if(status.equals("1")){
				        	acflag++;
				        	request.setAttribute("sc", acflag);
				        }
				        if(status.equals("2")){
				        	acflag=acflag+2;
				        	request.setAttribute("sc", acflag);
				        }
				        if(status.equals("3")){
				        	acflag=acflag+3;
				        	request.setAttribute("sc", acflag);
				        }
				        if(status.equals("4")){
				        	acflag=acflag+4;
				        	request.setAttribute("sc", acflag);
				        }
			      }
			      
			      rs.close();
			      stmt.close();
			    }catch (ClassNotFoundException e){
			    	
			    }catch (SQLException e){
			      
			    }catch (Exception e){
			    	
			    }finally{
			      try{
			        if (conn != null){
			          conn.close();
			        }
			      }catch (SQLException e){
			        
			      }
			    }
			    
			    
			    RequestDispatcher rd = request.getRequestDispatcher("AttendanceHistoryMonth.jsp");
				rd.forward(request,response);
		 		
		 	}
	}
}